<?php
class Model_CupKoGame extends Zend_Db_Table_Abstract
{
	protected $_name    = 'g1_cup_ko_game';
	protected $_primary = 'id';

	
	public function getGameById($game_id){
		return $this->find($game_id)->current();
	}

	public function generateAllGames($cup_id,$team_count,$type){
		
		    $round = 1;
			$count = 1;
			while( $team_count!=1){
 				for($i=0;$i<floor($team_count/2);$i++){
 					$data['cup_id']=$cup_id;
 					$data['round'] = $round;
 					$data['node_id'] = $count;
 					$this->insert($data);
 					if($type=="double"){
 						$data['cup_id']=$cup_id;
 						$data['round'] = $round;
 						$data['node_id'] = $count;
 						$this->insert($data);
 					}
 										
					$count++;							
				}
				
				$team_count = ceil($team_count/2);
				$round++;
			}			
	
	}
	
	public function getAllGamesByLeagueAndUser($league_id,$user_id){
		$select = "select lth.team_name as home_team_name , lta.team_name as away_team_name,
				          g.home_team_score , g.away_team_score
				   from g1_game g , g1_league_team lth,g1_league_team lta
				   where g.league_id=" . $league_id .
		           " and g.league_id = lth.league_id
		             and g.league_id = lta.league_id
		           	 and g.home_team_id = lth.team_id
		             and g.away_team_id = lta.team_id
		           	 and (g.home_team_id= " . $user_id . " or g.away_team_id = " . $user_id .")";
		
		return $this->_db->fetchAll( $select );
	}
	
	public function getAllPlayedGamesByCupAndUser($cup_id,$round,$user_id){
		$select = "select lth.team_name as home_team_name , lta.team_name as away_team_name,
				          g.home_team_score , g.away_team_score
				   from g1_cup_ko_game g , g1_cup_ko_team lth,g1_cup_ko_team lta
				   where g.cup_id=" . $cup_id .
					   " and g.cup_id = lth.cup_ko_id
					   and g.round='" . $round ."'
		             and g.cup_id = lta.cup_ko_id
		           	 and g.home_team_id = lth.team_id
		             and g.away_team_id = lta.team_id
					 and g.home_team_confirm = 'yes' 
					 and g.away_team_confirm = 'yes'
		           	 and (lth.user_id= " . $user_id . " or lta.user_id = " . $user_id .")";
	
		return $this->_db->fetchAll( $select );
	}
	
	public function getFinishedGamesByCupId($cup_id,$round){
		$select = "select g.id,lth.team_name as home_team_name , lta.team_name as away_team_name,
				          g.home_team_score , g.away_team_score,g.home_team_confirm ,g.away_team_confirm
				   from g1_cup_ko_game g , g1_cup_ko_team lth,g1_cup_ko_team lta
				   where g.cup_id=" . $cup_id .
					   " and g.cup_id = lth.cup_ko_id
					   	and g.round='" . $round ."'
		             and g.cup_id = lta.cup_ko_id
		           	 and g.home_team_id = lth.team_id
		             and g.away_team_id = lta.team_id
		           	and g.home_team_confirm = 'yes'
					 and g.away_team_confirm = 'yes'";

	
		return $this->_db->fetchAll( $select );
	}
	
	public function getToConfirmGamesByCupId($cup_id,$round){
		$select = "select g.id,lth.team_name as home_team_name , lta.team_name as away_team_name,
				          g.home_team_score , g.away_team_score,g.home_team_confirm ,g.away_team_confirm
				   from g1_cup_ko_game g , g1_cup_ko_team lth,g1_cup_ko_team lta
				   where g.cup_id=" . $cup_id .
					   " and g.cup_id = lth.cup_ko_id
					   	and g.round='" . $round ."'
		             and g.cup_id = lta.cup_ko_id
		           	 and g.home_team_id = lth.team_id
		             and g.away_team_id = lta.team_id
		           	 and ((g.home_team_confirm = 'yes' and g.away_team_confirm = 'no')or(
					   		g.home_team_confirm = 'no' and g.away_team_confirm = 'yes'))";
	
		return $this->_db->fetchAll( $select );
	}
	
	public function getToStartGamesByCupId($cup_id,$round){
		$select = "select g.id,lth.team_name as home_team_name , lta.team_name as away_team_name,
				          g.home_team_score , g.away_team_score,g.home_team_confirm ,g.away_team_confirm
				   from g1_cup_ko_game g , g1_cup_ko_team lth,g1_cup_ko_team lta
				   where g.cup_id=" . $cup_id .
					   " and g.cup_id = lth.cup_ko_id
					   	and g.round='" . $round ."'
		             and g.cup_id = lta.cup_ko_id
		           	 and g.home_team_id = lth.team_id
		             and g.away_team_id = lta.team_id
		           	 and g.home_team_confirm = 'no'
					 and g.away_team_confirm = 'no'";
	
		return $this->_db->fetchAll( $select );
	}
	
	public function getAllToConfirmGamesByCupAndUser($cup_id,$round,$user_id){
		$select = "select g.id, lth.team_name as home_team_name , lta.team_name as away_team_name,
				          g.home_team_score , g.away_team_score
				   from g1_cup_ko_game g , g1_cup_ko_team lth,g1_cup_ko_team lta
				   where g.cup_id=" . $cup_id .
					   " and g.cup_id = lth.cup_ko_id
					   and g.round='" . $round ."'
		             and g.cup_id = lta.cup_ko_id
		           	 and g.home_team_id = lth.team_id
		             and g.away_team_id = lta.team_id
					 and g.home_team_confirm = 'no'
					 and g.away_team_confirm = 'yes'
		           	 and lth.user_id= " . $user_id . 
		           " union select g2.id, lth2.team_name as home_team_name , lta2.team_name as away_team_name,
				          g2.home_team_score , g2.away_team_score
				   from g1_cup_ko_game g2 , g1_cup_ko_team lth2,g1_cup_ko_team lta2
				   where g2.cup_id=" . $cup_id .
					   " and g2.cup_id = lth2.cup_ko_id
					   and g2.round='" . $round ."'
		             and g2.cup_id = lta2.cup_ko_id
		           	 and g2.home_team_id = lth2.team_id
		             and g2.away_team_id = lta2.team_id
					 and g2.home_team_confirm = 'yes'
					 and g2.away_team_confirm = 'no'
		           	 and lta2.user_id= " . $user_id .
		           	 " order by id asc" ;

		return $this->_db->fetchAll( $select );
	}
	
	public function getAllNewGamesByCupAndUser($cup_id,$round,$user_id){
		$select = "select g.id,lth.team_name as home_team_name , lta.team_name as away_team_name,
				          g.home_team_score , g.away_team_score
				   from g1_cup_ko_game g , g1_cup_ko_team lth,g1_cup_ko_team lta
				   where g.cup_id=" . $cup_id .
					   " and g.cup_id = lth.cup_ko_id
					   and g.round='" . $round ."'
		             and g.cup_id = lta.cup_ko_id
		           	 and g.home_team_id = lth.team_id
		             and g.away_team_id = lta.team_id
					 and g.home_team_confirm = 'no'
					 and g.away_team_confirm = 'no'
		           	 and (lth.user_id= " . $user_id . " or lta.user_id = " . $user_id .")

		           	union
		           	 select g2.id,lth2.team_name as home_team_name , lta2.team_name as away_team_name,
				          g2.home_team_score , g2.away_team_score
				   from g1_cup_ko_game g2 , g1_cup_ko_team lth2,g1_cup_ko_team lta2
				   where g2.cup_id=" . $cup_id .
					   " and g2.cup_id = lth2.cup_ko_id
					   and g2.round='" . $round ."'
		             and g2.cup_id = lta2.cup_ko_id
		           	 and g2.home_team_id = lth2.team_id
		             and g2.away_team_id = lta2.team_id
					 and g2.home_team_confirm = 'yes'
					 and g2.away_team_confirm = 'no'
		           	 and lth2.user_id= " . $user_id . " 

		           	union
		           	 	select g3.id,lth3.team_name as home_team_name , lta3.team_name as away_team_name,
				          g3.home_team_score , g3.away_team_score
				   from g1_cup_ko_game g3 , g1_cup_ko_team lth3,g1_cup_ko_team lta3
				   where g3.cup_id=" . $cup_id .
					   " and g3.cup_id = lth3.cup_ko_id
					   and g3.round='" . $round ."'
		             and g3.cup_id = lta3.cup_ko_id
		           	 and g3.home_team_id = lth3.team_id
		             and g3.away_team_id = lta3.team_id
					 and g3.home_team_confirm = 'no'
					 and g3.away_team_confirm = 'yes'
		           	 and lta3.user_id = " . $user_id .
		            " order by id asc" ;
	
		return $this->_db->fetchAll( $select );
	}
	
	public function confirmGameByGameId($game_id){
		$data = array('home_team_confirm'  => 'yes',
				       'away_team_confirm' => 'yes');
		$this->update($data, 'id='.$game_id);
	}
	
	public function updateGameScoreByUserId($game_id,$user_id,$away_score,$home_score){
		$game = $this->find($game_id)->current();
		$data = array('home_team_score'  => $home_score,
				'away_team_score' => $away_score);
		
		$model_cup_ko_team = new Model_CupKoTeam();
		
		if($model_cup_ko_team->getCupTeamInfoByCupIdAndTeamId($game->cup_id,$game->home_team_id)->user_id == $user_id){
			$data['home_team_confirm'] = 'yes';
		}else{
			$data['away_team_confirm'] = 'yes';
		}
	
		$this->update($data, 'id='.$game_id);
       
	}
	
	public function getAllGamesByCupId($cup_id){
	    $select = "select *   from
				    g1_cup_ko_game kg 
				    where kg.cup_id = " . $cup_id .		                 
		            " order by node_id,id" ;
		
		return $this->_db->fetchAll( $select );
	}
	
	public function deleteGamesByCupIdAndTeamId($cup_id, $team_id){
		$delete = 'DELETE FROM g1_cup_game                         
                        WHERE cup_id='.$cup_id.' and (home_team_id =' .
                           $team_id .' or away_team_id = ' .$team_id .')';
		
		$this->_db->query( $delete );
	}
	
	public function clearGamesByLeagueIdAndTeamId($league_id, $team_id){
		$delete = 'update  g1_game set home_team_score=null ,away_team_score = null , 
				                       home_team_confirm="no",away_team_confirm="no"
                        WHERE league_id='.$league_id.' and (home_team_id =' .
	                        $team_id .' or away_team_id = ' .$team_id .')';
	
		$this->_db->query( $delete );
	}
	
	public function addGamesByLeagueIdAndTeamId($league_id, $team_id){
		$model_league = new Model_League();
		$league = $model_league->getLeagueById($league_id);
		
		$count = 1;
		for($count=0;$count<$league->team_count ;$count++){
			if(($count+1)!=$team_id){
				$data['league_id']=$league_id;
				$data['home_team_id'] = $count +1;
				$data['away_team_id'] = $team_id;
					
				$select = $this->select()
						->from($this, 'count(id) as count')
						->where('league_id = ?', $league_id)
						->where('home_team_id = ?',$count +1)	
						->where('away_team_id = ?',$team_id)	;
				 
				$exist = $this->fetchRow($select);
				
				$dispo = ($exist->count == 0);
				if($dispo){
					$this->insert($data);
				}
				
				
				
				$data['league_id']=$league_id;
				$data['home_team_id'] = $team_id;
				$data['away_team_id'] = $count +1;
					
				$select = $this->select()
						->from($this, 'count(id) as count')
						->where('league_id = ?', $league_id)
						->where('home_team_id = ?',$team_id)	
						->where('away_team_id = ?', $count +1)	;
				 
				$exist = $this->fetchRow($select);
				
				$dispo = ($exist->count == 0);
				if($dispo){
					$this->insert($data);
				}
			}		
		}
	}
	
	public function addGamesByCupIdAndTeamId($cup_id, $team_id){
		$model_cup = new Model_Cup();
		$cup = $model_cup->getCupById($cup_id);
		
		$model_cup_team = new Model_CupTeam();
		$team_group = $model_cup_team->getCupTeamInfoByCupIdAndTeamId($cup_id, $team_id)->group_name;

	
		$group_teams = $model_cup_team->getTeamsByGroup($cup_id,$team_group);

		foreach ($group_teams as $team){
			if($team->team_id!=$team_id){
				if(!empty($team->user_id)){
					$select = $this->select()
					->from($this, 'count(id) as count')
					->where('cup_id = ?', $cup_id)
					->where('home_team_id = ?',$team->team_id)
					->where('away_team_id = ?',$team_id)	;
						
					$exist = $this->fetchRow($select);
					
					$dispo = ($exist->count == 0);
					if($dispo){
						$data['cup_id']=$cup_id;
						$data['home_team_id'] = $team->team_id;
						$data['away_team_id'] = $team_id;
						$data['stage']='group-stage';
						$this->insert($data);
						
						$data['cup_id']=$cup_id;
						$data['home_team_id'] = $team_id;
						$data['away_team_id'] = $team->team_id;
						$data['stage']='group-stage';
						$this->insert($data);
					}
				}
			}
		}
		
		
	}
	
	public function getGamesByCupId($cup_id){
		$select = "select kg.node_id , ct1.team_name home_team_name, ct2.team_name away_team_name   from
				    g1_cup_ko_game kg left join g1_cup_ko_team ct1 on kg.home_team_id = ct1.team_id left join g1_cup_ko_team ct2
					on kg.away_team_id = ct2.team_id 
				    where kg.cup_id = " . $cup_id .
		                  " and ct1.cup_ko_id = " . $cup_id .
		                  " and ct2.cup_ko_id = " . $cup_id .
		            " order by kg.id asc" ;
		
		return $this->_db->fetchAll( $select );
	}

	public function modifyGame($cup_id,$node_id,$round,$away_team_name,$home_team_name){
		if(!empty($away_team_name) && !empty($home_team_name)){
			$model_cup_team = new Model_CupTeam();
			$away_team_id = $model_cup_team->getTeamByCupIdAndTeamName($cup_id,$away_team_name)->team_id;
			$home_team_id = $model_cup_team->getTeamByCupIdAndTeamName($cup_id,$home_team_name)->team_id;
			
			$select = $this->select()
					->where('cup_id = ?', $cup_id)
					->where('node_id = ?',$node_id);			
			$exist = $this->fetchRow($select);
				
			
			if(empty($exist)){				
				$data['cup_id']=$cup_id;
				$data['home_team_id'] = $home_team_id;
				$data['away_team_id'] = $away_team_id;
				$data['node_id']=$node_id;
				$data['round']=$round;
				$this->insert($data);
			
				$data['cup_id']=$cup_id;
				$data['home_team_id'] = $away_team_id;
				$data['away_team_id'] = $home_team_id;
				$data['node_id']=$node_id;
				$data['round']=$round;
				$this->insert($data);
			}else{ 
				if(($exist->home_team_id==$home_team_id && $exist->away_team_id==$away_team_id) || 
				  ($exist->home_team_id==$away_team_id && $exist->away_team_id==$home_team_id)){
					
				}else{
					$delete = 'DELETE FROM g1_cup_ko_game
                        WHERE cup_id='.$cup_id.' and node_id=' . $node_id;
					$this->_db->query( $delete );
					$data['cup_id']=$cup_id;
					$data['home_team_id'] = $home_team_id;
					$data['away_team_id'] = $away_team_id;
					$data['node_id']=$node_id;
					$data['round']=$round;
					$this->insert($data);
						
					$data['cup_id']=$cup_id;
					$data['home_team_id'] = $away_team_id;
					$data['away_team_id'] = $home_team_id;
					$data['node_id']=$node_id;
					$data['round']=$round;
					$this->insert($data);
					
				}
			}
			
		}else{
			$select = $this->select()
			->where('cup_id = ?', $cup_id)
			->where('node_id = ?',$node_id);
			$exist = $this->fetchRow($select);
			
			if(!empty($exist)){
				$delete = 'DELETE FROM g1_cup_ko_game
                        WHERE cup_id='.$cup_id.' and node_id=' . $node_id;
				$this->_db->query( $delete );
			}
			return ;
		}
	}
	
	public function updateGame($cup_id,$node_id,$away_team_name,$home_team_name,$type){
			$model_cup_ko_team = new Model_CupKoTeam();
			$away_team_id = $model_cup_ko_team->getTeamByCupIdAndTeamName($cup_id,$away_team_name)->team_id;
			$home_team_id = $model_cup_ko_team->getTeamByCupIdAndTeamName($cup_id,$home_team_name)->team_id;
			
			if(empty($away_team_id) || empty($home_team_id)){
				$data['home_team_id'] = NULL;
				$data['away_team_id'] =	NULL;	
				$data['home_team_score'] = NULL;
				$data['away_team_score'] = NULL;	
				$data['home_team_confirm'] = 'no';
				$data['away_team_confirm'] = 'no';					
				$this->update($data, 'cup_id=' . $cup_id . ' and node_id = ' . $node_id);
			}else{
				$select = $this->select()
							->where('cup_id = ?', $cup_id)
							->where('node_id = ?',$node_id)
							->where('away_team_id = ?',$away_team_id)
							->where('home_team_id = ?',$home_team_id);
				$exist = $this->fetchRow($select);
				
				if(empty($exist)){
					if($type=='single'){
						$select = $this->select('id')
							->where('cup_id = ?', $cup_id)
							->where('node_id = ?',$node_id);
						$ids = $this->fetchAll($select);
						$data['home_team_id'] = $home_team_id;
						$data['away_team_id'] =	$away_team_id;	
						$data['home_team_score'] = NULL;
						$data['away_team_score'] = NULL;	
						$data['home_team_confirm'] = 'no';
						$data['away_team_confirm'] = 'no';					
						$this->update($data, 'id=' . $ids[0]->id);
					}else{
						$select = $this->select('id')
							->where('cup_id = ?', $cup_id)
							->where('node_id = ?',$node_id);
						$ids = $this->fetchAll($select);
						$data['home_team_id'] = $home_team_id;
						$data['away_team_id'] =	$away_team_id;	
						$data['home_team_score'] = NULL;
						$data['away_team_score'] = NULL;	
						$data['home_team_confirm'] = 'no';
						$data['away_team_confirm'] = 'no';					
						$this->update($data, 'id=' . $ids[0]->id);
						$data['home_team_id'] = $away_team_id;
						$data['away_team_id'] =	$home_team_id;	
						$data['home_team_score'] = NULL;
						$data['away_team_score'] = NULL;	
						$data['home_team_confirm'] = 'no';
						$data['away_team_confirm'] = 'no';					
						$this->update($data, 'id=' . $ids[1]->id);
					}
				}
				return ;
			}
			
			
			
	}	 
	
	
		public function updateGameScoreByAdminUser($game_id,$away_score,$home_score){
		$game = $this->find($game_id)->current();
		$data = array('home_team_score'  => $home_score,
				'away_team_score' => $away_score);
	
		$data['home_team_confirm'] = 'yes';
		$data['away_team_confirm'] = 'yes';
	
	
		$this->update($data, 'id='.$game_id);
			
	}
}